*******************************************************
** Replicate McMillan plots using TZ data


import excel using "Comparisons context.xlsx" , sheet(Diao2018graph) firstrow clear
keep in 1/10

regress sectoralprodtotalprodlog changeempshareannualized
graph twoway (scatter sectoralprodtotalprodlog changeempshareannualized [w= empsharebase], msymbol(circle_hollow) mcolor(black) mlabcolor(black)) (scatter sectoralprodtotalprodlog changeempshareannualized, msymbol(none) mlabel(Sector) mlabcolor(black)) (lfit sectoralprodtotalprodlog changeempshareannualized [w= empsharebase]), xline(0, lcolor(black) lpattern(dash)) yline(0, lcolor(black) lpattern(dash)) legend(off) xlab(-.1 (.1) .5,nogrid) ylab(-1(0.5)1.5,nogrid) xtitle("Change in employment share (annualized)") ytitle("Sector productivity / Total Productivity (log)") graphregion(color(white)) bgcolor(white)
graph export "McMillan plot tz data.pdf", replace
graph export "McMillan plot tz data.png", replace



*******************************************************
** Africa Sector Database
** https://www.rug.nl/ggdc/structuralchange/previous-sector-database/africa-sector-database
** This code will create a trend in labor shares by African country from 1960-2010

import excel using "asd_oct_2013_yl.xlsx", sheet("DATA") firstrow clear

loc seclist ag ind_manu ind_mining ser_biz ser_constr ser_gov ser_personal ser_trade ser_transport ser_utilities
rename Sectorname Sector 
clonevar Sec = Sector

replace Sec = "ag" if Sec=="Agriculture"
replace Sec = "ser_biz" if Sec=="Business services"
replace Sec = "ser_constr" if Sec=="Construction"
*replace Sec = "ser_housing" if Sec=="Dwellings" /*this is part of biz services - ignore*/
replace Sec = "ser_gov" if Sec=="Government services"
replace Sec = "ind_manu" if Sec=="Manufacturing"
replace Sec = "ind_mining" if Sec=="Mining"
replace Sec = "ser_personal" if Sec=="Personal services"
replace Sec = "ser_trade" if Sec=="Trade services"
replace Sec = "ser_transport" if Sec=="Transport services"
replace Sec = "ser_utilities" if Sec=="Utilities"
replace Sec = "tot" if Sec=="Total Economy"

reshape long _, i(Country Countryname Variable Sec) j(year)
drop if _==.

drop ISICcode Sector
reshape wide _, i(Country Countryname  Sec year) j(Variable) string
rename _* *_

reshape wide EMP_ EMP_F_ EMP_M_ VA_ VA_P_ VA_Q05_, i(Country Countryname year) j(Sec) string

foreach sec in `seclist' {
	g EMPSH_`sec' = EMP_`sec'/EMP_tot
	g VASH_`sec' = (VA_`sec' / VA_tot)
	g VAPW_`sec' = (VA_`sec' / EMP_`sec') / (VA_tot / EMP_tot) 
	}
	
foreach var in EMPSH VASH {
	egen `var'_ind = rowtotal(`var'_ind_manu `var'_ind_mining)
	egen `var'_ser = rowtotal(`var'_ser_biz `var'_ser_constr `var'_ser_gov `var'_ser_personal `var'_ser_trade `var'_ser_transport `var'_ser_utilities)
	}

g VAPW_ind = (1/EMPSH_ind) * (VAPW_ind_manu * EMPSH_ind_manu + VAPW_ind_mining * EMPSH_ind_mining)
g VAPW_ser = (1/EMPSH_ser) * ///
			 (  VAPW_ser_biz * EMPSH_ser_biz ///
			  + VAPW_ser_constr * EMPSH_ser_constr ///
			  + VAPW_ser_gov * EMPSH_ser_gov ///
			  + VAPW_ser_personal * EMPSH_ser_personal ///
			  + VAPW_ser_trade * EMPSH_ser_trade ///
			  + VAPW_ser_transport * EMPSH_ser_transport ///
			  + VAPW_ser_utilities * EMPSH_ser_utilities) 
			  

foreach sec in ag ind ser {
	recode EMPSH_`sec' (0=.)
	recode VASH_`sec' (0=.)
	}


tempfile asd_clean
save `asd_clean', replace


** Now order countries 

tab Country
levelsof Country, local(countrylist)

loc BWA_lab "Botswana"
loc ETH_lab "Ethiopia"
loc GHA_lab "Ghana"
loc KEN_lab "Kenya"
loc MUS_lab "Mauritius"
loc MWI_lab "Malawi"
loc NGA_lab "Nigeria"
loc SEN_lab "Senegal"
loc TZA_lab "Tanzania"
loc ZAF_lab "South Africa"
loc ZMB_lab "Zambia"


* get price info
import excel using "asd_july_2013_ppps.xlsx", sheet("DATA") firstrow clear
keep if Variable=="xr"
keep Country _2005
foreach c in `countrylist' {
	su _2005 if Country=="`c'"
	loc `c'_pr = r(mean) /*this is the exchange rate*/
	}


* [Get value added per worker in baseline year]
foreach c in `countrylist' {
	use `asd_clean', clear
	keep if Country=="`c'"
	keep if VA_Q05_tot != . & EMP_tot != .
	sort year 
	keep in 1
	g VA_PW = (VA_Q05_tot / EMP_tot) / ``c'_pr'
	su VA_PW
	loc `c'_vapw = r(mean)
	}

* [Get 1990 gdp per capita (constant 2017 international $)]
loc BWA_gdppp 9164.872508
loc ETH_gdppp 767.1704186
loc GHA_gdppp 2299.638794
loc KEN_gdppp 3655.362057
loc MUS_gdppp 8233.911912
loc MWI_gdppp 936.9862997
loc NGA_gdppp 3259.581175
loc SEN_gdppp 2469.603188
loc TZA_gdppp 1338.019043
loc ZAF_gdppp 10391.96414
loc ZMB_gdppp 2290.039226

clear
set obs 11
g Country = ""
g ordervar = .

loc i = 1
foreach c in `countrylist' {
	replace Country = "`c'" in `i'
	replace ordervar = ``c'_vapw' in `i' 
	/*In the line above I am ordering by value added per worker in earliest observed year in constant 2005 dollars*/ 
	**To instead order by 1990 gdp per capita from WDI use ``c'_gdppp' 
	loc i = `i'+1
	}
	
sort ordervar

loc countrylist_ord ""
forvalues i = 1/11 {
	loc val = Country in `i'
	loc countrylist_ord `countrylist_ord' `val'
	}
display "`countrylist_ord'"
list Country ordervar


** Now graph emp share trends for these countries, in order
* First for employemnt shares

use `asd_clean', clear

loc grcommand_ag ""
loc grcommand_ind ""
loc grcommand_ser ""
loc legcommand ""

loc i = 1
foreach c in `countrylist_ord' {
	loc legcommand `legcommand' `i' "`c'"
	display "`c'"
	foreach sec in ag ind ser {
		loc grcommand_`sec' `grcommand_`sec'' (line EMPSH_`sec' year if Country== "`c'") 
		}
	loc i = `i'+1
	}
	
	display `"`grcommand_ag'"'

foreach sec in ag ind ser {
	graph twoway `grcommand_`sec'', legend(order(`legcommand')) ytitle("Employment share in `sec'") legend(cols(4)) graphregion(color(white)) bgcolor(white)
	sleep 500
	graph export "ASD_Empshare_`sec'.pdf", replace
	}


* Next for VA per worker 

use `asd_clean', clear

loc grcommand_ag ""
loc grcommand_ind ""
loc grcommand_ser ""
loc legcommand ""


loc i = 1
foreach c in `countrylist_ord' {
	loc legcommand `legcommand' `i' "`c'"
	display "`c'"
	foreach sec in ag ind ser {
		loc grcommand_`sec' `grcommand_`sec'' (line VAPW_`sec' year if Country== "`c'") 
		}
	loc i = `i'+1
	}
	
	display `"`grcommand_ag'"'

foreach sec in ag ind ser {
	graph twoway `grcommand_`sec'', legend(order(`legcommand')) ytitle("Value added per worker in `sec'") legend(cols(4)) graphregion(color(white)) bgcolor(white)
	sleep 500
	graph export "ASD_ValueAddedPerWorker_`sec'.pdf", replace
	}

	
** Now make area share graphs

* First for Employment Shares
drop if EMPSH_ag == . | EMPSH_ind == . | EMPSH_ser == .
g rarea_empsh_base = 0
g rarea_empsh_ag = EMPSH_ag
g rarea_empsh_ind = EMPSH_ag+EMPSH_ind
g rarea_empsh_ser = EMPSH_ag+EMPSH_ind+EMPSH_ser

loc grc1leglist ""

foreach c in `countrylist_ord' {
	graph twoway ///
		(rarea rarea_empsh_base rarea_empsh_ag year if Country=="`c'", lwidth(none) fcolor(red%50)) ///
		(rarea rarea_empsh_ag rarea_empsh_ind year if Country=="`c'", lwidth(none) fcolor(green%50)) ///
		(rarea rarea_empsh_ind rarea_empsh_ser year if Country=="`c'", lwidth(none) fcolor(blue%50)) /// 
		||, ///
			yscale(range(0 1)) ylabel(0(.1)1, nogrid)  ///
			title("`c'") ytitle( "Employment Share") xtitle("") ///
			legend(rows(1) order (1 "Ag" 2 "Ind" 3 "Ser")) ///
			graphregion(color(white)) bgcolor(white) ///
			name(Empshare_rarea_`c', replace) 
	loc grc1leglist `grc1leglist' Empshare_rarea_`c'
	}
	

grc1leg `grc1leglist', graphregion(color(white))
graph export "Empshare_rarea_all.pdf", replace


* Now for emp share by subsector

use `asd_clean', clear

g rarea_empsh_base = 0
g rarea_cum = rarea_empsh_base
foreach sec in `seclist' {
	drop if EMPSH_`sec' == . 
	g rarea_empsh_`sec' = rarea_cum + EMPSH_`sec'
	replace rarea_cum = rarea_cum + EMPSH_`sec'
	}

loc grc1leglist ""

foreach c in `countrylist_ord' {
	graph twoway ///
		(rarea rarea_empsh_base rarea_empsh_ag year if Country=="`c'", lwidth(none) fcolor(red%50) ) ///
		(rarea rarea_empsh_ag rarea_empsh_ind_manu year if Country=="`c'", lwidth(none) fcolor(green%25)) ///
		(rarea rarea_empsh_ind_manu rarea_empsh_ind_mining year if Country=="`c'", lwidth(none) fcolor(green%75)) ///
		(rarea rarea_empsh_ind_mining rarea_empsh_ser_biz year if Country=="`c'", lwidth(none) fcolor(blue%10)) ///
		(rarea rarea_empsh_ser_biz rarea_empsh_ser_constr year if Country=="`c'", lwidth(none) fcolor(blue%20)) ///
		(rarea rarea_empsh_ser_constr rarea_empsh_ser_gov year if Country=="`c'", lwidth(none) fcolor(blue%30)) ///
		(rarea rarea_empsh_ser_gov rarea_empsh_ser_personal year if Country=="`c'", lwidth(none) fcolor(blue%40)) ///
		(rarea rarea_empsh_ser_personal rarea_empsh_ser_trade year if Country=="`c'", lwidth(none) fcolor(blue%50)) ///
		(rarea rarea_empsh_ser_trade rarea_empsh_ser_transport year if Country=="`c'", lwidth(none) fcolor(blue%60)) ///
		(rarea rarea_empsh_ser_transport rarea_empsh_ser_utilities year if Country=="`c'", lwidth(none) fcolor(blue%70)) ///
		||, ///
			yscale(range(0 1)) ylabel(0(.2)1, nogrid)  ///
			title("`c'") ytitle( "Employment Share") xtitle("") ///
			legend(cols(3) symy(2) symx(8) size(tiny) region(lstyle(none)) ///
			order (/*- "Agriculture" - "Industry" - "Services"*/ 1 "Agriculture" 2 "Ind - manufacturing" 4 "Ser - business" - " " 3 "Ind - mining" 5 "Ser - construction" - " " - " "  6 "Ser - govt"  - " " - " " 7 "Ser - personal" - " " - " " 8 "Ser - trade" - " " - " " 9 "Ser - transport" - " " - " " 10 "Ser - utilities ")) ///
			graphregion(color(white)) bgcolor(white) ///
			name(Empshare_rarea_subsec_`c', replace) 
	loc grc1leglist `grc1leglist' Empshare_rarea_subsec_`c'
	}
	
grc1leg `grc1leglist', graphregion(color(white)) 
graph export "Empshare_rarea_subsec_all.pdf", replace


** Now graph area share for value added shares 

use `asd_clean', clear

drop if VASH_ag == . | VASH_ind == . | VASH_ser == .
g rarea_vash_base = 0
g rarea_vash_ag = VASH_ag
g rarea_vash_ind = VASH_ag + VASH_ind
g rarea_vash_ser = VASH_ag + VASH_ind + VASH_ser

loc grc1leglist ""

foreach c in `countrylist_ord' {
	graph twoway ///
		(rarea rarea_vash_base rarea_vash_ag year if Country=="`c'", lwidth(none) fcolor(red%50) ) ///
		(rarea rarea_vash_ag rarea_vash_ind year if Country=="`c'", lwidth(none) fcolor(green%50)) ///
		(rarea rarea_vash_ind rarea_vash_ser year if Country=="`c'", lwidth(none) fcolor(blue%50)) /// 
		||, ///
			yscale(range(0 1)) ylabel(0(.1)1, nogrid)  ///
			title("`c'") ytitle( "Value Added Share") xtitle("") ///
			legend(rows(1) order (1 "Ag" 2 "Ind" 3 "Ser")) ///
			graphregion(color(white)) bgcolor(white) ///
			name(VAshare_rarea_`c', replace)
	loc grc1leglist `grc1leglist' VAshare_rarea_`c'
	}
	

grc1leg `grc1leglist', graphregion(color(white))
graph export "VAshare_rarea_all.pdf", replace


** Now graph area share for value added shares - subsector

use `asd_clean', clear

g rarea_vash_base = 0
g rarea_cum = rarea_vash_base
foreach sec in `seclist' {
	drop if VASH_`sec' == . 
	g rarea_vash_`sec' = rarea_cum + VASH_`sec'
	replace rarea_cum = rarea_cum + VASH_`sec'
	}

loc grc1leglist ""

foreach c in `countrylist_ord' {
	graph twoway ///
		(rarea rarea_vash_base rarea_vash_ag year if Country=="`c'", lwidth(none) fcolor(red%50) ) ///
		(rarea rarea_vash_ag rarea_vash_ind_manu year if Country=="`c'", lwidth(none) fcolor(green%25)) ///
		(rarea rarea_vash_ind_manu rarea_vash_ind_mining year if Country=="`c'", lwidth(none) fcolor(green%75)) ///
		(rarea rarea_vash_ind_mining rarea_vash_ser_biz year if Country=="`c'", lwidth(none) fcolor(blue%10)) ///
		(rarea rarea_vash_ser_biz rarea_vash_ser_constr year if Country=="`c'", lwidth(none) fcolor(blue%20)) ///
		(rarea rarea_vash_ser_constr rarea_vash_ser_gov year if Country=="`c'", lwidth(none) fcolor(blue%30)) ///
		(rarea rarea_vash_ser_gov rarea_vash_ser_personal year if Country=="`c'", lwidth(none) fcolor(blue%40)) ///
		(rarea rarea_vash_ser_personal rarea_vash_ser_trade year if Country=="`c'", lwidth(none) fcolor(blue%50)) ///
		(rarea rarea_vash_ser_trade rarea_vash_ser_transport year if Country=="`c'", lwidth(none) fcolor(blue%60)) ///
		(rarea rarea_vash_ser_transport rarea_vash_ser_utilities year if Country=="`c'", lwidth(none) fcolor(blue%70)) ///
		||, ///
			yscale(range(0 1)) ylabel(0(.2)1, nogrid)  ///
			title("`c'") ytitle( "Value Added Share") xtitle("") ///
			legend(cols(3) symy(2) symx(8) size(tiny) region(lstyle(none)) ///
			order (/*- "Agriculture" - "Industry" - "Services"*/ 1 "Agriculture" 2 "Ind - manufacturing" 4 "Ser - business" - " " 3 "Ind - mining" 5 "Ser - construction" - " " - " "  6 "Ser - govt"  - " " - " " 7 "Ser - personal" - " " - " " 8 "Ser - trade" - " " - " " 9 "Ser - transport" - " " - " " 10 "Ser - utilities ")) ///
			graphregion(color(white)) bgcolor(white) ///
			name(VAshare_rarea_subsec_`c', replace)
	loc grc1leglist `grc1leglist' VAshare_rarea_subsec_`c'
	}
	
grc1leg `grc1leglist', graphregion(color(white)) 
graph export "VAshare_rarea_subsec_all.pdf", replace


grc1leg Empshare_rarea_subsec_TZA VAshare_rarea_subsec_TZA, graphregion(color(white))
graph export "TZA_rarea_subsec.pdf", replace


